use common
go

IF OBJECT_ID('dbo.p_GetNextIdentity') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_GetNextIdentity
    IF OBJECT_ID('dbo.p_GetNextIdentity') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_GetNextIdentity >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_GetNextIdentity >>>'
END
go

create proc p_GetNextIdentity
(
	@DbName			varchar(40),		-- database name
    @TblName 		varchar(40),		-- table name
	@Seed			int=1,			-- seed to increase next avail id
	@CompanyName 	varchar(40),		-- CompanyName
	@LastId			int 	output
) as
Begin

set nocount on 

	-- declare
		declare @Msg 		varchar(255)
        	declare @SrvName 	varchar(40)
		declare @ErrVal		int
 
	-- initialize
        select @LastId=null
		select @SrvName=@@servername 
		select @ErrVal=0

	-- validate
		if not exists( 	select 1 
				from common..t_IdentityControl
				where DbName=@DbName and TblName=@TblName)
		begin
			select @Msg="/DbName "+isnull(@DbName,"")+
					"/TblName "+isnull(@TblName,"")+
					" is not defined in common..t_IdentityControl table"
			select @ErrVal = 99999
			goto ErrorHandler
		end

		if @Seed < 0
		begin
			select @Msg="Seed parameter cannot be negative !"
			select @ErrVal = 99999
			goto ErrorHandler
		end	
   
    update 	common..t_IdentityControl 		
	set      LastId=LastId+@Seed 
      	, @LastId=LastId+@Seed   -- this is the key part, we assign value to a variable as part of the update statement.
  	where 	DbName=@DbName
	and	TblName=@TblName 
	and CompanyName=@CompanyName
select @ErrVal=@@error

if (@ErrVal!=0 or @LastId is null)
begin
	select @Msg = "Error generating new Last Id."
	select @ErrVal = 99999
	goto ErrorHandler	
end
select @LastId=@LastId-@Seed
return 0 


ErrorHandler:
	select @LastId=null
	raiserror @ErrVal @Msg
End

go
EXEC sp_procxmode 'dbo.p_GetNextIdentity','anymode'
go
IF OBJECT_ID('dbo.p_GetNextIdentity') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_GetNextIdentity >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_GetNextIdentity >>>'
go
GRANT EXECUTE ON dbo.p_GetNextIdentity TO public
go
